#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import sys
import _mysql, _mysql_exceptions
from collections import defaultdict
from pytools import mysqltool
from pytools import hbaseservice

#获取table_prefix
def get_channel_info():
    q = 'select channel_id, table_prefix from channel_define'
    info = {}
    conn = mysqltool.connect_to_base_dicts()
    r = mysqltool.query(conn, q)
    for one in r:
        info[one[0]] = one[1]
    conn.close()
    return info

fields = ['stat_date', 'dispcity1', 'dispcity2', 'dispcity3', 'belong_cate1', 'belong_cate2', 'total_info_count', 'total_info_users', 'valid_info_count', 'valid_info_users', 'new_info_count', 'new_info_users', 'modify_info_count', 'modify_info_users', 'refresh_info_count', 'refresh_info_users', 'new_valid_info_count', 'new_valid_info_users', 'ordinary_valid_info_count', 'ordinary_valid_info_users', 'ordinary_new_info_count', 'ordinary_new_info_users', 'ordinary_modify_info_count', 'ordinary_modify_info_users', 'ordinary_refresh_info_count', 'ordinary_refresh_info_users', 'ordinary_new_valid_info_count', 'ordinary_new_valid_info_users', 'vip_valid_info_count', 'vip_valid_info_users', 'vip_new_info_count', 'vip_new_info_users', 'vip_modify_info_count', 'vip_modify_info_users', 'vip_refresh_info_count', 'vip_refresh_info_users', 'vip_new_valid_info_count', 'vip_new_valid_info_users']
def get_detail_fieldname_index():
       return dict(zip(fields, range(len(fields))))

def get_info(q):
    info = {}
    conn = mysqltool.connect_to_58_stats()
    r = mysqltool.query(conn, q)
    for one in r:
        info[one[0]] = one[1:]
    # print len(info)
    conn.close()
    return info

def get_cate_info():
     sql = 'select cate_id, cate1, cate2, channel_id from belong_cate where cate3_id=0'
     return get_info(sql)

def get_displocal_info():
    q = 'select local_id, city1, city2, city3 from display_local where city4_id=0'
    return get_info(q)

def writeDetailToMysql(connect,taleprefix,rowdata):    
    tablename = taleprefix + '_post_detail'
    mysqltool.insertinto(connect, tablename, fields, rowdata)

def writeSummaryToMysql(connect,hbase_service, day):
    rowkey='A_'+day
    rs = hbase_service.queryByRowKey('FourthInfo', rowkey)
    if not rs:
        return
    tablename = 'post_summary'
    fields = ['stat_date', 'infos', 'users']
    values = [day,rs.get('cf:InfoCount'),rs.get('cf:UserCount')]
    mysqltool.insertinto(connect, tablename, fields, values)

onetwothree = ['first', 'second', 'third']
fieldname2index = get_detail_fieldname_index()
num2level = dict(zip(range(1, 4), onetwothree))
counttype = ['InfoCount', 'UserCount']

states = ['state1', 'uptype0', 'uptype1', 'uptype2', 'state1_uptype0']
types = ['valid', 'new', 'modify', 'refresh', 'new_valid']
states2types = dict(zip(states, types))

sources = ['source0', 'source6']
roles = ['ordinary', 'vip'] 
sources2roles = dict(zip(sources, roles))


def set_rowdata(rowdata,hbase_service,fieldname,key):
    rowkey = '_'.join((key))
    rs = hbase_service.queryByRowKey('FourthInfo', rowkey)
    rowdata[fieldname2index(fieldname+'_info_count')] = rs.get('cf:InfoCount')
    rowdata[fieldname2index(fieldname+'_info_users')] = rs.get('cf:UserCount')

def compose_mysql_rows(day, cateinfos, localinfos,channel_info):
    mysqlConnect = mysqltool.connect_to_58_stats()
    hbase_service =hbaseservice.HBaseService()
    rowdata = ['0'] * len(fields)
    rowdata[fieldname2index['stat_date']] = day
    for cateid, cinfo in cateinfos.items():
        cate_level = 'cate' + num2level[2 - cinfo.count('')]
        for localid, linfo in localinfos.items():
            b = fieldname2index['belong_cate1']
            for i, a in enumerate(cinfo):
                rowdata[b + i] = a
            b = fieldname2index['dispcity1']   
            for i, a in enumerate(linfo):
                rowdata[b + i] = a
            local_level = 'local' + num2level[3 - linfo.count('')]
            key=(cate_level, local_level, day)
            set_rowdata(rowdata,hbase_service,'total',key)
            for state in states:
                statekey=(cate_level, local_level, state, day)
                set_rowdata(rowdata,hbase_service,states2types(state),statekey)
                for source in sources:
                    sourcekey = (cate_level, local_level, state, source, day)
                    set_rowdata(rowdata,hbase_service,sources2roles[source] + '_' + states2types[state],sourcekey)
            print(rowdata)
            writeDetailToMysql(mysqlConnect,channel_info[cateid],rowdata)
    writeSummaryToMysql()
    mysqlConnect.close()
    hbase_service.shutdown()
            
if __name__ == '__main__':
    if len(sys.argv) != 2:
        print 'usage: %s day' % (sys.argv[0])
        sys.exit(1)
    day = sys.argv[1]
    # day = '20121220'

    # infile = 'data/hoho.txt'
    infile = 'data/' + day + '.txt'  
    channel_info=get_channel_info() 
    cate_infos = get_cate_info()
    local_infos = get_displocal_info()
    compose_mysql_rows(day,cate_infos,local_infos,channel_info)
